#!/usr/local/bin/python
# -*- coding: utf-8 -*-

from datetime import datetime, date

from sqlalchemy import Column, Integer, String, Text, Float, Date, and_, text
from sqlalchemy.ext.declarative import declarative_base

import init
import initAlipayPage
import toolkit
from init import session
from initLogger import logger

# engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8")


Base = declarative_base()


class AlipayVoucherPreviewAlchemy(Base):
    __tablename__ = "alipay_voucher_preview"

    id = Column(Integer, primary_key=True, autoincrement=True)
    account_date = Column(Date)
    year = Column(Integer, comment="")
    month = Column(Integer, comment="")
    voucher_no = Column(Text)
    summary = Column(Text)

    # property_increase_name_0 = Column(Text, comment="资产增加名称_0")
    # property_increase_name_1 = Column(Text, comment="资产增加名称_1")
    # property_increase_name_2 = Column(Text, comment="资产增加名称_2")
    # property_increase_name_3 = Column(Text, comment="资产增加名称_3")
    # property_increase_name_4 = Column(Text, comment="资产增加名称_4")
    property_increase_name = Column(Text, comment="资产增加名称")
    property_increase_value = Column(Float, comment="资产增加金额")

    # property_decrease_name_0 = Column(Text, comment="资产减少名称_0")
    # property_decrease_name_1 = Column(Text, comment="资产减少名称_1")
    # property_decrease_name_2 = Column(Text, comment="资产减少名称_2")
    # property_decrease_name_3 = Column(Text, comment="资产减少名称_3")
    # property_decrease_name_4 = Column(Text, comment="资产减少名称_4")
    property_decrease_name = Column(Text)
    property_decrease_value = Column(Float)

    # ownership_increase_name_0 = Column(Text, comment="权属增加名称_0")
    # ownership_increase_name_1 = Column(Text, comment="权属增加名称_1")
    # ownership_increase_name_2 = Column(Text, comment="权属增加名称_2")
    # ownership_increase_name_3 = Column(Text, comment="权属增加名称_3")
    # ownership_increase_name_4 = Column(Text, comment="权属增加名称_4")
    # ownership_increase_name_5 = Column(Text, comment="权属增加名称_5")
    ownership_increase_name = Column(Text)
    ownership_increase_value = Column(Float)

    # ownership_decrease_name_0 = Column(Text, comment="权属减少名称_0")
    # ownership_decrease_name_1 = Column(Text, comment="权属减少名称_1")
    # ownership_decrease_name_2 = Column(Text, comment="权属减少名称_2")
    # ownership_decrease_name_3 = Column(Text, comment="权属减少名称_3")
    # ownership_decrease_name_4 = Column(Text, comment="权属减少名称_4")
    # ownership_decrease_name_5 = Column(Text, comment="权属减少名称_5")
    ownership_decrease_name = Column(Text)
    ownership_decrease_value = Column(Float)

    voucher_type = Column(Text)  # 用于描述记账凭证的类型



    def __init__(self,
                 accountDate=datetime.strptime("1900-01-01", '%Y-%m-%d'),
                 voucherNo="000",
                 summary="",
                 propertyIncreaseName="",
                 propertyIncreaseValue=0.0,
                 propertyDecreaseName="",
                 propertyDecreaseValue=0.0,
                 ownershipIncreaseName="",
                 ownershipIncreaseValue=0.0,
                 ownershipDecreaseName="",
                 ownershipDecreaseValue=0.0,
                 voucherType=initAlipayPage.ALIPAY_GENERAL_VOUCHER_TYPE
                 ):

        self.account_date = accountDate
        self.year = accountDate.year
        self.month = accountDate.month
        self.voucher_no = voucherNo
        self.summary = summary

        #资产增加
        propertyIncreaseItems = toolkit.splitVoucher(propertyIncreaseName)
        # self.property_increase_name_0 = propertyIncreaseItems[0]
        # self.property_increase_name_1 = propertyIncreaseItems[1]
        # self.property_increase_name_2 = propertyIncreaseItems[2]
        # self.property_increase_name_3 = propertyIncreaseItems[3]
        # self.property_increase_name_4 = propertyIncreaseItems[4]

        self.property_increase_name = propertyIncreaseName
        self.property_increase_value = propertyIncreaseValue

        #资产减少
        propertyDecreaseItems = toolkit.splitVoucher(propertyDecreaseName)
        # self.property_decrease_name_0 = propertyDecreaseItems[0]
        # self.property_decrease_name_1 = propertyDecreaseItems[1]
        # self.property_decrease_name_2 = propertyDecreaseItems[2]
        # self.property_decrease_name_3 = propertyDecreaseItems[3]
        # self.property_decrease_name_4 = propertyDecreaseItems[4]

        self.property_decrease_name = propertyDecreaseName
        self.property_decrease_value = propertyDecreaseValue

        #权属增加
        ownershipIncreaseItems = toolkit.splitVoucher(ownershipIncreaseName)
        # self.ownership_increase_name_0 = ownershipIncreaseItems[0]
        # self.ownership_increase_name_1 = ownershipIncreaseItems[1]
        # self.ownership_increase_name_2 = ownershipIncreaseItems[2]
        # self.ownership_increase_name_3 = ownershipIncreaseItems[3]
        # self.ownership_increase_name_4 = ownershipIncreaseItems[4]
        # self.ownership_increase_name_5 = ownershipIncreaseItems[5]

        self.ownership_increase_name = ownershipIncreaseName
        self.ownership_increase_value = ownershipIncreaseValue

        #权属减少
        ownershipDecreaseItems = toolkit.splitVoucher(ownershipDecreaseName)
        # self.ownership_decrease_name_0 = ownershipDecreaseItems[0]
        # self.ownership_decrease_name_1 = ownershipDecreaseItems[1]
        # self.ownership_decrease_name_2 = ownershipDecreaseItems[2]
        # self.ownership_decrease_name_3 = ownershipDecreaseItems[3]
        # self.ownership_decrease_name_4 = ownershipDecreaseItems[4]
        # self.ownership_decrease_name_5 = ownershipDecreaseItems[5]

        self.ownership_decrease_name = ownershipDecreaseName
        self.ownership_decrease_value = ownershipDecreaseValue

        self.voucher_type = voucherType

    def AddVoucherItem(self):
        '''
        函数功能：添加一条凭证数据
        :return:
        '''
        session.add(self)
        #session.commit()

    def CommitVoucherData(self):
        '''
        函数功能：将缓冲中的数据写入数据库
        :return:
        '''
        session.commit()

    def DelVoucherItem(self, id):
        '''
        函数功能：删除一条凭证数据
        :param id:
        :return: 成功返回True，失败返回False
        '''
        deleteVoucher = session.query(AlipayVoucherPreviewAlchemy).filter(AlipayVoucherPreviewAlchemy.id == id).first()
        if deleteVoucher:
            session.delete(deleteVoucher)
            session.commit()
            return True
        else:
            return False


    def ModifiyVoucherItem(self, id):
        '''
        函数功能：将指定id在数据库中的内容，修改成新的内容
        :param id:
        :return:
        '''
        # 有需求时再具体实现

        queryResult = session.query(AlipayVoucherPreviewAlchemy).filter_by(id=id).first()
        queryResult.account_date = self.account_date
        queryResult.voucher_no= self.voucher_no
        queryResult.summary =self.summary

        # queryResult.property_increase_name_0 = self.property_increase_name_0
        # queryResult.property_increase_name_1 = self.property_increase_name_1
        # queryResult.property_increase_name_2 = self.property_increase_name_2
        # queryResult.property_increase_name_3 = self.property_increase_name_3
        # queryResult.property_increase_name_4 = self.property_increase_name_4
        queryResult.property_increase_name = self.property_increase_name
        queryResult.property_increase_value = self.property_increase_value


        # queryResult.property_decrease_name_0 = self.property_decrease_name_0
        # queryResult.property_decrease_name_1 = self.property_decrease_name_1
        # queryResult.property_decrease_name_2 = self.property_decrease_name_2
        # queryResult.property_decrease_name_3 = self.property_decrease_name_3
        # queryResult.property_decrease_name_4 = self.property_decrease_name_4
        queryResult.property_decrease_name = self.property_decrease_name
        queryResult.property_decrease_value = self.property_decrease_value

        # queryResult.ownership_increase_name_0 = self.ownership_increase_name_0
        # queryResult.ownership_increase_name_1 = self.ownership_increase_name_1
        # queryResult.ownership_increase_name_2 = self.ownership_increase_name_2
        # queryResult.ownership_increase_name_3 = self.ownership_increase_name_3
        # queryResult.ownership_increase_name_4 = self.ownership_increase_name_4
        # queryResult.ownership_increase_name_5 = self.ownership_increase_name_5
        queryResult.ownership_increase_name = self.ownership_increase_name
        queryResult.ownership_increase_value = self.ownership_increase_value

        # queryResult.ownership_decrease_name_0 = self.ownership_decrease_name_0
        # queryResult.ownership_decrease_name_1 = self.ownership_decrease_name_1
        # queryResult.ownership_decrease_name_2 = self.ownership_decrease_name_2
        # queryResult.ownership_decrease_name_3 = self.ownership_decrease_name_3
        # queryResult.ownership_decrease_name_4 = self.ownership_decrease_name_4
        # queryResult.ownership_decrease_name_5 = self.ownership_decrease_name_5
        queryResult.ownership_decrease_name = self.ownership_decrease_name
        queryResult.ownership_decrease_value = self.ownership_decrease_value

        session.commit()



    def SearchVoucherByDate(self, startDate, endDate):
        '''
        函数功能：按照时间查找记账凭证
        :return:
        '''
        '''
        voucherInfo = session.query(AlipayVoucherPreviewAlchemy).filter(and_(AlipayVoucherPreviewAlchemy.account_date >= startDate, AlipayVoucherPreviewAlchemy.account_date <= endDate))\
                                                          .order_by(AlipayVoucherPreviewAlchemy.account_date, AlipayVoucherPreviewAlchemy.id) \
                                                          .all()
        '''
        voucherInfo = session.query(AlipayVoucherPreviewAlchemy).filter(and_(AlipayVoucherPreviewAlchemy.account_date >= startDate, AlipayVoucherPreviewAlchemy.account_date <= endDate))\
                                                          .order_by(AlipayVoucherPreviewAlchemy.summary, AlipayVoucherPreviewAlchemy.account_date) \
                                                          .all()

        return voucherInfo

    def SearchVoucheByID(self, id):
        '''
        函数功能：按照时间查找记账凭证
        :return:
        '''
        users = session.query(AlipayVoucherPreviewAlchemy).filter_by(id=id).all()
        for item in users:
            item.ShowVoucher()
        return users[0]

    def ShowVoucher(self):
        voucherInfor = ("记账凭证：\n"
                        "\t凭证id：%s \n"
                        "\t凭证名称：%s \n"
                        "\t时间：%s \n "
                        "\t凭证编号：%s \n\n"
                        "\t资产增加名称：%s \n"
                        "\t资产增加金额：%.2f\n\n"
                        "\t资产减少名称：%s \n"
                        "\t资产减少金额：%.2f \n\n"
                        "\t权属增加名称：%s \n"
                        "\t权属增加金额：%.2f \n\n"
                        "\t权属减少名称：%s \n"
                        "\t权属减少金额：%.2f \n"
                        "") % (self.id,
                               self.summary,
                               self.account_date,
                               self.voucher_no,
                               self.property_increase_name,
                               self.property_increase_value,
                               self.property_decrease_name,
                               self.property_decrease_value,
                               self.ownership_increase_name,
                               self.ownership_increase_value,
                               self.ownership_decrease_name,
                               self.ownership_decrease_value)
        logger.debug(voucherInfor)

        return voucherInfor


    def GetDataCount(self):
        '''
        函数功能：获取数据库中数据的条数
        :return:
        '''
        count = session.query(AlipayVoucherPreviewAlchemy).count()
        return count
    def DelAllDataFromTable(self):
        '''
        函数功能；清空表格中的数据
        :return:
        '''

        session.query(AlipayVoucherPreviewAlchemy).delete()
        session.commit()


def main():
    # accountingVoucher = AccountingVoucher("test")

    # test_SearchByid()

    test_AddDataItem()

    # Base.metadata.create_all(engine)


def test_SearchByid():
    accountingVoucher = AlipayVoucherPreviewAlchemy()
    accountingVoucher.SearchVoucheByID(11)


def test_AddDataItem():
    currentDate = datetime.now()
    logger.debug(currentDate)
    logger.debug(currentDate.date())
    myDate = str(currentDate.date())
    logger.debug("qqqq:" + myDate)
    voucher_no = "%s_%d" % (str(currentDate.date()), 134)
    accountingVoucherPreview = AlipayVoucherPreviewAlchemy(accountDate=(date(2022, 3, 12)),
                                                           voucherNo=voucher_no,
                                                           summary="记账",
                                                           propertyIncreaseName="工资",
                                                           propertyIncreaseValue=200.12,
                                                           propertyDecreaseName="",
                                                           propertyDecreaseValue=0.0,
                                                           ownershipIncreaseName="5月份收入",
                                                           ownershipIncreaseValue=200.12,
                                                           ownershipDecreaseName="",
                                                           ownershipDecreaseValue=0.0)
    # accountingVoucher
    accountingVoucherPreview.ShowVoucher()
    logger.debug(accountingVoucherPreview)
    accountingVoucherPreview.AddVoucherItem()



#创建数据库表
AlipayVoucherPreviewAlchemy.metadata.create_all(init.engine)
if __name__ == '__main__':
    main()
